package com.gl.saasservice.matchUser.service;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.Optional;
import java.util.stream.Collectors;

import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.alibaba.fastjson.JSON;
import com.gl.common.util.SecurityUtils;
import com.gl.common.util.sql.SqlUtils;
import com.gl.common.vo.ApiData;
import com.gl.framework.security.LoginUser;
import com.gl.framework.web.response.Result;
import com.gl.saasservice.entity.AppUser;
import com.gl.saasservice.entity.CommunityHouseUser;
import com.gl.saasservice.entity.MatchUser;
import com.gl.saasservice.entity.MatchUserLockAuthor;
import com.gl.saasservice.entity.SmartLockAuth;
import com.gl.saasservice.entity.SmartLockUser;
import com.gl.saasservice.house.service.HouseService;
import com.gl.saasservice.matchUser.vo.MatchUserAuthorVO;
import com.gl.saasservice.matchUser.vo.MatchUserHouseVO;
import com.gl.saasservice.matchUser.vo.MatchUserLockAuthorVO;
import com.gl.saasservice.matchUser.vo.MatchUserVO;
import com.gl.saasservice.repository.AppUserRepository;
import com.gl.saasservice.repository.CommunityHouseUserRepository;
import com.gl.saasservice.repository.MatchUserAuthorRepository;
import com.gl.saasservice.repository.MatchUserHouseRepository;
import com.gl.saasservice.repository.MatchUserLockAuthorRepository;
import com.gl.saasservice.repository.MatchUserRepository;
import com.gl.saasservice.repository.QYUserRepository;
import com.gl.saasservice.repository.SmartLockAuthRepository;
import com.gl.saasservice.repository.SmartLockUserRepository;
import com.gl.saasservice.sys.vo.AppUserVO;


@Service
public class MatchUserService {
	private static final Logger log = LoggerFactory.getLogger(HouseService.class);

	@Autowired
	private MatchUserAuthorRepository matchUserAuthorRepository;

	@Autowired
	private MatchUserLockAuthorRepository matchUserLockAuthorRepository;

	@Autowired
	private MatchUserHouseRepository matchUserHouseRepository;

	@Autowired
	private MatchUserRepository matchUserRepository;

	@Autowired
	private AppUserRepository userRepository;

	@Autowired
	private QYUserRepository qyUserRepository;

	@Autowired
	private SmartLockAuthRepository smartLockAuthRepository;

	@Autowired
	private SmartLockUserRepository smartLockUserRepository;

	@Autowired
	private CommunityHouseUserRepository communityHouseUserRepository;

	@Autowired
	private JdbcTemplate jdbcTemplate;

	private static final String count = " SELECT COUNT(1) ";

	/**
	 * 通过名字或者手机号码模糊查询, 默认根据创建日期降序 支持登记时间、承租人姓名（升序降序排列）
	 *
	 * @return Result
	 */
	public Result findByMatchUserAndCreateTimeDesc(String token, MatchUserVO vo) {
		log.info("matchUserService pageslist token={}, vo={}", token, vo.toString());
		try {
			String result = " SELECT st.*,hu.number AS houseNumber";
			String SQL = "  FROM m_match_user_stock st \n"
					+ " LEFT JOIN (SELECT h.match_user_stock_id, COUNT(h.house_id) AS number FROM m_match_user_house h GROUP BY h.match_user_stock_id) hu"
					+ " ON hu.match_user_stock_id = st.id " + " LEFT JOIN saas_app_user sau"
					+ " ON st.craete_user_id = sau.id ";

			StringBuilder where = new StringBuilder(" WHERE 1 = 1 and delete_status = 0 ");
			List<Object> args = new ArrayList<>();

			if (vo != null) {
				// 承租人姓名
				if (StringUtils.isNotBlank(vo.getName())) {
					where.append(" AND st.name like ? \n");
					args.add("%" + vo.getName() + "%");
				}
				// 手机号码
				if (StringUtils.isNotBlank(vo.getPhone())) {
					where.append(" AND st.phone like ? \n");
					args.add("%" + vo.getPhone() + "%");
				}
			}

			StringBuffer orderby = new StringBuffer();
			if (StringUtils.isBlank(vo.getSortName()) && StringUtils.isBlank(vo.getSortcreateTime())) {
				orderby = new StringBuffer(" ORDER BY st.create_time desc ");
			}
			if (StringUtils.isNotBlank(vo.getSortcreateTime())) {
				orderby = new StringBuffer(" ORDER BY st.create_time ").append(vo.getSortcreateTime()).append(" \n");
			}
			// 排序
			if (StringUtils.isNotBlank(vo.getSortName())) {
				orderby = new StringBuffer(" ORDER BY convert(st.name using gbk) collate gbk_chinese_ci ")
						.append(vo.getSortName()).append(" \n");
			}

			Long total = jdbcTemplate.queryForObject(count + SQL + where, Long.class, args.toArray());
			ApiData<MatchUserVO> apiData = new ApiData<>();
			if (total == 0) {
				return Result.success(apiData);
			}
			String offsets = " limit ? offset ?";
			args.add(vo.getPageSize());
			args.add(vo.getPageNumber() * vo.getPageSize());
			List<MatchUserVO> data = jdbcTemplate.query(result + SQL + where + orderby + offsets,
					new BeanPropertyRowMapper<>(MatchUserVO.class), args.toArray());

			String sql = "SELECT id,NAME  FROM saas_app_user au WHERE au.id = ?\n" + "UNION ALL\n"
					+ "SELECT id, NAME FROM saas_user su WHERE su.id = ? ";

			data.forEach(item -> {
				List<Object> a = new ArrayList<>();
				a.add(item.getCraeteUserId());
				a.add(item.getCraeteUserId());
				List<AppUserVO> query = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(AppUserVO.class),
						a.toArray());
				query.forEach(i -> {
					if (i.getId() != null) {
						if (i.getId().equals(item.getCraeteUserId())) {
							item.setCreateUserName(i.getName());
						}
					}
				});
			});

			for (MatchUserVO datum : data) {
				if (datum.getHouseNumber() == null) {
					datum.setHouseNumber(0);
				}
			}
			apiData.setTotal(total);
			apiData.setData(data);
			return Result.success(apiData);
		} catch (Exception e) {
			log.error(e.getMessage(), e);
			return Result.fail();
		}
	}

	/**
	 * 编辑
	 *
	 * @param token
	 * @param vo
	 * @return
	 */
	@Transactional(rollbackFor = Exception.class)
	public Result updateNameAndPhone(String token, MatchUserVO vo) {
		log.info("matchUserRepository   communityProperty   token ={} , vo={} ", token, vo.toString());

		if (StringUtils.isBlank(vo.getName())) {
			return Result.fail("承租人姓名不能为空");
		}
		if (StringUtils.isBlank(vo.getPhone())) {
			return Result.fail("手机号码不能为空");
		}
		Optional<MatchUser> communityOptional = matchUserRepository.findById(vo.getId());
		if(!communityOptional.isPresent()){
			return Result.fail("承租人信息有误");
		}

		MatchUser entity = communityOptional.get();
		// 查询并更新APP_USER
		AppUser appUser = userRepository.findByPhoneAndStatus(entity.getPhone(),1);
		if(appUser != null){
			if(!entity.getPhone().equals(vo.getPhone())){
				appUser.setPhone(vo.getPhone());
			}
			appUser.setName(vo.getName());
			userRepository.save(appUser);

			if(appUser.getEnterpriseUserId() != null){
				// 查询并更新SYS_USER
				qyUserRepository.updateUserPhone(vo.getName(), vo.getPhone(), appUser.getEnterpriseUserId());
			}
		}

		// 智能锁更新
		List<SmartLockAuth> byMobile = smartLockAuthRepository.findByMobile(entity.getPhone());
		if (byMobile != null) {
			byMobile.forEach(ba ->{
				ba.setMobile(vo.getPhone());
				ba.setUserName(vo.getName());
			});

			smartLockAuthRepository.saveAll(byMobile);
		}
		
		// 智能锁安装承租人姓名
		String updateSql = "UPDATE saas_smart_lock lk SET match_user_name=? WHERE house_id in (SELECT community_house_id FROM saas_community_house_user u WHERE u.mobile = ? AND u.type = 1 AND u.status = 1)";
	    jdbcTemplate.update(updateSql, vo.getName(), entity.getPhone());

		// 个人锁用户表
		List<SmartLockUser> byMobile1 = smartLockUserRepository.findByMobile(entity.getPhone());
		if (byMobile1 != null) {
			byMobile1.forEach(ba ->{
				ba.setMobile(vo.getPhone());
				ba.setUserName(vo.getName());
			});

			smartLockUserRepository.saveAll(byMobile1);
		}

		// 房产成员表
		List<CommunityHouseUser> communityHouseUsers = communityHouseUserRepository.findByMobile(entity.getPhone());
		if (communityHouseUsers != null) {
			communityHouseUsers.forEach(ba ->{
				ba.setMobile(vo.getPhone());
				ba.setName(vo.getName());
			});

			communityHouseUserRepository.saveAll(communityHouseUsers);
		}

		entity.setName(vo.getName());
		entity.setPhone(vo.getPhone());
		matchUserRepository.save(entity);

		return Result.success(null);
	}

	/**
	 * 查询该承租人的房屋
	 *
	 * @return
	 */
	public Result findhouseDetailByid(MatchUserHouseVO vo) {
		// 连接
		try {
			String result = "SELECT distinct sc.`name` AS communityname,scb.`name` AS buildname,scbu.`name` AS unitname,sh.room_number AS roomnumber,pro.`NAME` AS province,ct.`NAME` AS city,dist.`NAME` AS district,muh.label,muh.company_name,muh.social_credit_number, "
					+ " sh.residence_community_id AS residenceCommunityId,sh.building_num AS buildingNum,sh.unit,sh.province_id AS provinceId,sh.city_id AS cityId,sh.district_id AS districtId ";
			String SQL = " FROM m_match_user_house muh\n" + "LEFT JOIN saas_house sh\n" + "on sh.id = muh.house_id\n"
					+ "LEFT JOIN saas_community sc\n" + "ON sh.residence_community_id = sc.id\n"
					+ "LEFT JOIN saas_community_build scb\n" + "ON sh.building_num = scb.id\n"
					+ "LEFT JOIN saas_community_build_unit scbu\n" + "ON sh.unit = scbu.id\n"
					+ "LEFT JOIN sys_area pro\n" + "ON sh.province_id = pro.id\n" + "LEFT JOIN sys_area ct\n"
					+ "ON sh.city_id = ct.id\n" + "LEFT JOIN sys_area dist\n" + "ON sh.district_id = dist.id ";
			StringBuffer where = new StringBuffer(" WHERE 1=1 ");
			List<Object> args = new ArrayList<>();
			if (vo != null) {
				if (vo.getMatchUserStockId() != null) {
					where.append(" AND muh.match_user_stock_id = ? ");
					args.add(vo.getMatchUserStockId());
				}
			}

			// 查询条件
			if (vo != null) {
				// 小区id
				if (StringUtils.isNotBlank(vo.getResidenceCommunityId())) {
					where.append(" and sh.residence_community_id = ?");
					args.add(vo.getResidenceCommunityId());
				}
				// 楼宇id
				if (vo.getBuildingNum() != null) {
					where.append(" and sh.building_num = ?");
					args.add(vo.getBuildingNum());
				}
				// 单元id
				if (vo.getUnit() != null) {
					where.append(" and sh.unit = ?");
					args.add(vo.getUnit());
				}
				// 房号
				if (StringUtils.isNotBlank(vo.getRoomnumber())) {
					where.append(" and sh.room_number = ?");
					args.add(vo.getRoomnumber());
				}

				if (vo.getLabel() != null) {
					if (vo.getLabel() == 1 || vo.getLabel() == 2) {
						where.append(" and muh.label = ?  ");
						args.add(vo.getLabel());
					}
				}

				// 省
				if (vo.getProvinceId() != null) {
					where.append(" and sh.province_id = ? ");
					args.add(vo.getProvinceId());
				}
				// 市
				if (vo.getCityId() != null) {
					where.append(" and sh.city_id = ? ");
					args.add(vo.getCityId());
				}
				// 区
				if (vo.getDistrictId() != null) {
					where.append(" and sh.district_id = ? ");
					args.add(vo.getDistrictId());
				}
				// 单位名称
				if (StringUtils.isNotBlank(vo.getCompanyName())) {
					where.append(" and muh.company_name like ? ");
					args.add("%" + vo.getCompanyName() + "%");
				}
				// 社会信用代码
				if (StringUtils.isNotBlank(vo.getSocialCreditNumber())) {
					where.append(" and muh.social_credit_number like ? ");
					args.add("%" + vo.getSocialCreditNumber() + "%");
				}

			}

			ApiData<MatchUserHouseVO> apiData = new ApiData<>();
			Long total = jdbcTemplate.queryForObject(count + SQL + where, Long.class, args.toArray());
			if (total == 0) {
				return Result.success(apiData);
			}
			List<MatchUserHouseVO> query = jdbcTemplate.query(result + SQL + where,
					new BeanPropertyRowMapper<>(MatchUserHouseVO.class), args.toArray());

			for (MatchUserHouseVO matchUserHouseVO : query) {
				if (matchUserHouseVO.getLabel() == 1) {
					matchUserHouseVO.setUserRole("个人承租人");
				}
				if (matchUserHouseVO.getLabel() == 2) {
					matchUserHouseVO.setUserRole("单位承租人");
				}
				matchUserHouseVO.setPlace(
						matchUserHouseVO.getProvince() + matchUserHouseVO.getCity() + matchUserHouseVO.getDistrict());
			}

			apiData.setTotal(total);
			apiData.setData(query);
			return Result.success(apiData);
		} catch (Exception e) {
			log.error(e.getMessage(), e);
			return Result.fail();
		}
	}

	/**
	 * 针对承租人授权信息设置表
	 *
	 * @param vo
	 * @return
	 */
	public Result saveOrupdateAuthorNumber(MatchUserAuthorVO vo) {
		log.info("MatchUserService saveOrupdateAuthorNumber, vo={}", JSON.toJSONString(vo));
		try {
			//LoginUser loginUser = SecurityUtils.getLoginUser();
			if(StringUtils.isBlank(vo.getMatchUserStockId())){
				return Result.fail("承租人id不能为空");
			}

			// 批量设置承租人授权次数与密码有效期
			String[] checkedMatch = vo.getMatchUserStockId().split(",");
			List<Object> argList = new ArrayList<>();
			String fieldName = "author_number";
			if(vo.getAuthorNumber() != null){
				argList.add(vo.getAuthorNumber());
			}
			
			if(vo.getPasswordValidity() != null){
				argList.add(vo.getPasswordValidity());
				fieldName = "password_validity";
			}
			
			for (int i = 0; i < checkedMatch.length; i++) {
				argList.add(checkedMatch[i]);
			}
			// 重置所有承租人房屋临时密码有效期设置
			String updateSql = "UPDATE m_match_user_lock_author SET "+fieldName+"=? WHERE match_user_stock_id IN " + SqlUtils.foreachIn(checkedMatch.length);
			jdbcTemplate.update(updateSql, argList.toArray());
			return Result.success(null);
		} catch (Exception e) {
			log.error(e.getMessage(), e);
			return Result.fail();
		}
	}

	/**
	 * 针对承租人锁授权信息设置表
	 *
	 * @param vo
	 * @return
	 */
	public Result saveAuthorNumber(MatchUserLockAuthorVO vo) {
		LoginUser loginUser = SecurityUtils.getLoginUser();
		log.info("MatchUserService saveAuthorNumber, vo={}", JSON.toJSONString(vo));
		try {
			if(vo.getMatchUserStockId() == null){
				return Result.fail("承租人id不能为空");
			}
			List<MatchUserLockAuthor> data = new ArrayList<>();
			// 是否选择房屋
			List<String> houseIds = null;
			if(StringUtils.isNotBlank(vo.getHouseIds())){
				houseIds = Arrays.asList(vo.getHouseIds().split(","));
			}else{
				// 按承租人查询所有
				/*data = matchUserLockAuthorRepository.findByMatchUserStockId(vo.getMatchUserStockId());
				data.forEach(da ->{
					da.setAuthorNumber(vo.getAuthorNumber());
					da.setPasswordValidity(vo.getPasswordValidity());
					da.setLastUpdateTime(new Date());
				});*/
				List<MatchUserLockAuthorVO> list = findAllAuthor(Long.parseLong(vo.getMatchUserStockId()));
				if(list != null && !list.isEmpty()){
					houseIds = list.stream().map(t -> t.getHouseId()).collect(Collectors.toList());
				}
			}
			
			if(houseIds == null){
				data = matchUserLockAuthorRepository.findByMatchUserStockId(vo.getMatchUserStockId());
				data.forEach(da ->{
					da.setAuthorNumber(vo.getAuthorNumber());
					da.setPasswordValidity(vo.getPasswordValidity());
					da.setLastUpdateTime(new Date());
				});
			}else{
				List<MatchUserLockAuthor> list = matchUserLockAuthorRepository.findByHouseIdIn(houseIds);
				Map<String, MatchUserLockAuthor> map = list.stream().collect(Collectors.toMap(MatchUserLockAuthor::getHouseId, MatchUserLockAuthor -> MatchUserLockAuthor));
				for (String houseId : houseIds) {
					// 查询承租人设置记录表
					MatchUserLockAuthor byMatchUserStock = map.get(houseId);
					if(byMatchUserStock == null){
						// 未设置过
						byMatchUserStock = new MatchUserLockAuthor();
						byMatchUserStock.setCreateTime(new Date());
						byMatchUserStock.setUseNumber(0);
						byMatchUserStock.setOperatorUserId(loginUser.getUser().getId());
						// 生效年月
						SimpleDateFormat dateFormat = new SimpleDateFormat("yyyyMM");
						byMatchUserStock.setEffectiveJulyDate(dateFormat.format(new Date()));
						byMatchUserStock.setHouseId(houseId);
					}
					
					byMatchUserStock.setAuthorNumber(vo.getAuthorNumber());
					byMatchUserStock.setPasswordValidity(vo.getPasswordValidity());
					byMatchUserStock.setMatchUserStockId(vo.getMatchUserStockId());
					byMatchUserStock.setLastUpdateTime(new Date());
					
					data.add(byMatchUserStock);
				}
			}
			matchUserLockAuthorRepository.saveAll(data);
			return Result.success(null);
		} catch (Exception e) {
			log.error(e.getMessage(), e);
			return Result.fail();
		}
	}

	/**
	 * 授权临时密码，智能锁列表展示数据接口
	 */
	public Result lockpageslist(MatchUserVO vo) {
		try {
			ApiData<MatchUserLockAuthorVO> apiData = new ApiData<>();
			List<MatchUserLockAuthorVO> maps = findAllAuthor(vo.getId());
			if (maps != null && !maps.isEmpty()) {
				apiData.setData(maps);
			}

			return Result.success(apiData);
		} catch (Exception e) {
			log.error(e.getMessage(), e);
			return Result.fail();
		}
	}

	public List<MatchUserLockAuthorVO> findAllAuthor(Long id){
		String sql = "SELECT distinct sh.id AS houseId,muh.match_user_stock_id AS matchUserStockId,sc.name AS communityname,scb.name AS buildname,"
				+ "scbu.name AS unitname,sh.room_number AS roomnumber,la.author_number,la.password_validity "
				+ "FROM m_match_user_house muh "
				+ "LEFT JOIN m_match_user_lock_author la ON muh.house_id = la.house_id "
				+ "LEFT JOIN saas_house sh ON muh.house_id = sh.id "
				+ "LEFT JOIN saas_community sc ON sh.residence_community_id = sc.id "
				+ "LEFT JOIN saas_community_build scb ON sh.building_num = scb.id "
				+ "LEFT JOIN saas_community_build_unit scbu ON sh.unit = scbu.id WHERE muh.match_user_stock_id = ?";

		List<MatchUserLockAuthorVO> maps = jdbcTemplate.query(sql,
				new BeanPropertyRowMapper<>(MatchUserLockAuthorVO.class), id);
		if (maps != null && !maps.isEmpty()) {
			for (MatchUserLockAuthorVO map : maps) {
				if (map.getUseNumber() == null) {
					map.setUseNumber(0);
				}
				if (map.getAuthorNumber() == null) {
					map.setAuthorNumber(0);
				}
				if (map.getPasswordValidity() == null) {
					map.setPasswordValidity(0);
				}
			}
		}
		
		return maps;
	}
}
